package com.tianqu.tms.dao;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.TmsConstants;

public class ViewListDao extends TmsDao{

	protected ViewListDao() throws TmsDaoException {
		super();
	}

	/**
	 * 
	 */
	private String SQL_FROM = "FROM";
	/**
	 * 
	 */
	private String SQL_PAGING = "ROW_NUMBER()OVER() AS PAGING ";

	/**
	 * 
	 */
	private int resultFrom = 0;
	/**
	 * 
	 */
	private int resultTo = 0;
	/**
	 * 列表总数
	 */
	private int totalCount = 0;
	
	/**
	 * 目前第几页
	 */
	private int pageNo = 1;
	
	/**
	 * 显示条数
	 */
	private int maxIndex = 30;
	/**
	 * 排序键值
	 */
	private int sortKey = 1;
	
	private boolean asc = true;
	
	protected ViewListDao(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException {
		super();
		this.setPageNo(pageNo);
		this.setMaxIndex(maxIndex);
		this.setSortKey(sortKey);
		this.setAsc(asc);
		this.setTotalCount(totalCount);
	}

	
	protected String getOrderBySql(String oderBySqlList[]){
		if(oderBySqlList == null || sortKey > oderBySqlList.length || sortKey < 1) {
			return TmsConstants.BLANK;
		}
		StringBuffer sb = new StringBuffer(" ORDER BY ");

		sb.append(oderBySqlList[sortKey - 1]);
		if (isAsc()){
			sb.append(" ASC ");
		}else{
			sb.append(" DESC ");
		}
		return sb.toString();
	}
	
	public String getSql(String sql) throws TmsDaoException{
		if(sql.indexOf(SQL_FROM) != -1){
			String sql_select_from_where = TmsConstants.BLANK;
			if(sql.indexOf("ORDER")!=-1) {
			//fromを基準として、SQLを切る
				sql_select_from_where = sql.substring(0,sql.lastIndexOf("ORDER")).trim();
			} else {
				sql_select_from_where = sql; 
			}
			//total countの取得
			StringBuffer sb = new StringBuffer("SELECT COUNT(*) AS COUNT FROM ( ");
			sb.append(sql_select_from_where);
			sb.append(" ) AS a");
			getResultTotalCount(sb.toString());
			//result scopeの取得
			getResultScope();
			//sqlの組み合わせ
			
			sql = sql+" LIMIT "+resultFrom+","+maxIndex;
			resultFrom = resultFrom+1;//起始条数
		}
		
		return sql;
	}
	
	
	private void getResultTotalCount(String sql) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// SQL文を構造する。
			ps = conn.prepareStatement(sql);
			
			logger.debug("SQL=[" + sql + "]");
			
			// SQL文を実行する
			rs = ps.executeQuery();
			//データの取得
			while (rs.next()) {
				totalCount = rs.getInt("COUNT");
			}
			
		} catch (SQLException e) {
			throw new TmsDaoException(ERROR_ID,e);
			
		} finally {
			close(rs,ps);
		}
	}
	
	private void getResultScope(){
		if(totalCount != 0) {
			/*
			 * 2010/08/10 start liu gang 削除した後で、一覧へ戻す場合、
			 * 元のデータが少なくなって、画面異常になるというdefectの対応
			 */
			if((pageNo-1) * maxIndex >= totalCount) {
				pageNo = pageNo-1;
			}
			/*
			 * 2010/08/10 end liu gang 
			 */
			resultFrom = (pageNo-1) * maxIndex ;//limit是从m开始截取N位  从0开始
//			if()
//			{
//				
//			}
			resultTo =  pageNo*maxIndex ;
			if((pageNo *maxIndex) > totalCount) {
				resultTo = totalCount;
			}
		}
	}
	public String getSQL_FROM() {
		return SQL_FROM;
	}

	public void setSQL_FROM(String sQLFROM) {
		SQL_FROM = sQLFROM;
	}

	public String getSQL_PAGING() {
		return SQL_PAGING;
	}

	public void setSQL_PAGING(String sQLPAGING) {
		SQL_PAGING = sQLPAGING;
	}

	public int getResultFrom() {
		return resultFrom;
	}

	public void setResultFrom(int resultFrom) {
		this.resultFrom = resultFrom;
	}

	public int getResultTo() {
		return resultTo;
	}

	public void setResultTo(int resultTo) {
		this.resultTo = resultTo;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}

	public int getPageNo() {
		return pageNo;
	}

	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}

	public int getMaxIndex() {
		return maxIndex;
	}

	public void setMaxIndex(int maxIndex) {
		this.maxIndex = maxIndex;
	}

	public int getSortKey() {
		return sortKey;
	}

	public void setSortKey(int sortKey) {
		this.sortKey = sortKey;
	}

	public boolean isAsc() {
		return asc;
	}

	public void setAsc(boolean asc) {
		this.asc = asc;
	}
	
}
